﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Logistics_Service.DTO;
using System.Data;

namespace Logistics_Service.DAO
{
    public class DonHangDAO
    {
        public bool ThemDonHang(DonHangDTO dh)
        {
            bool flag = false;
            int daxoa = 1;
            
            string sql=string.Format("insert into DonHang values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}',{13},{14},'{15}',{16})",dh.MaDonHang,dh.NgayDat,dh.NgayLayHang,dh.DiaChiLayHang,dh.MaLoaiVanChuyen,dh.TenNguoiNhan,dh.CMND,dh.SDT,dh.DiaChiNguoiNhan,dh.MaGoiHang,dh.MaKhachHang,dh.TenChuThe,dh.SoTaiKhoan,dh.TongTien,dh.TinhTrang,dh.LoaiDatHang,daxoa);
            DataProvider dp = new DataProvider();
            flag = dp.ExecuteNonQuery(sql);
            return flag;
        }
        protected object GetDataFromDataRow(DataTable dt, int i)
        {
            DSDonHangDTO dh = new DSDonHangDTO();
            dh.MaDonHang = dt.Rows[i]["Ma"].ToString();
            dh.NgayDat = dt.Rows[i]["NgayDat"].ToString();
            dh.NgayLayHang = dt.Rows[i]["NgayLay"].ToString();
            dh.DiaChiLayHang = dt.Rows[i]["DiaChiLayHang"].ToString();
            dh.TenLoaiVanChuyen= dt.Rows[i]["Tenlvc"].ToString();
            dh.TenNguoiNhan = dt.Rows[i]["TenNguoiNhan"].ToString();
            dh.CMND = dt.Rows[i]["CMND"].ToString();
            dh.SDT = dt.Rows[i]["SDT"].ToString();
            dh.DiaChiNguoiNhan = dt.Rows[i]["DiaChiNguoiNhan"].ToString();
            dh.MaGoiHang = dt.Rows[i]["MaGoiHang"].ToString();
            dh.TenKhachHang = dt.Rows[i]["Tenkh"].ToString();
            dh.TenChuThe = dt.Rows[i]["TenChuThe"].ToString();
            dh.SoTaiKhoan = dt.Rows[i]["SoTaiKhoan"].ToString();
            dh.TongTien = float.Parse(dt.Rows[i]["TongTien"].ToString());
            dh.TenTinhTrang = dt.Rows[i]["TinhTrang"].ToString();
            dh.LoaiDatHang = dt.Rows[i]["LoaiDatHang"].ToString();
            return (object)dh;
        }
        public DSDonHangDTO[] DanhSachDonHang(string makhachhang)
        {
            DSDonHangDTO[] arr;
            string query = string.Format("select dh.ma,CONVERT(varchar(10), dh.ngaydat,103) as ngaydat,CONVERT(varchar(10), dh.ngaylay,103) as ngaylay,dh.diachilayhang,lvc.ten as tenlvc,dh.tennguoinhan,dh.cmnd,dh.sdt,dh.diachinguoinhan,dh.magoihang,kh.ten as tenkh,dh.tenchuthe,dh.sotaikhoan,dh.tongtien,tt.ten as tinhtrang,dh.loaidathang  from DonHang dh, TinhTrangDonHang tt, KhachHang kh, GoiHang gh, LoaiVanChuyen lvc where dh.MaGoiHang = gh.Ma and dh.MaLoaiVanChuyen = lvc.Ma and dh.MaKhachHang = kh.Ma and dh.TinhTrang = tt.Ma and dh.makhachhang=N'" + makhachhang + "'");
            DataProvider dp = new DataProvider();
            DataTable dt = dp.ExecuteQuery(query);
            int i, n = dt.Rows.Count;
            arr = new DSDonHangDTO[n];
            for (i = 0; i < n; i++)
            {
                object gh = GetDataFromDataRow(dt, i);
                arr[i] = (DSDonHangDTO)gh;
            }
            return arr;
        }
        public DSDonHangDTO[] DanhSachDonHangDangThucHien(string makhachhang)
        {
            DSDonHangDTO[] arr;
            string query = string.Format("select dh.ma,CONVERT(varchar(10), dh.ngaydat,103) as ngaydat,CONVERT(varchar(10), dh.ngaylay,103) as ngaylay,dh.diachilayhang,lvc.ten as tenlvc,dh.tennguoinhan,dh.cmnd,dh.sdt,dh.diachinguoinhan,dh.magoihang,kh.ten as tenkh,dh.tenchuthe,dh.sotaikhoan,dh.tongtien,tt.ten as tinhtrang,dh.loaidathang  from DonHang dh, TinhTrangDonHang tt, KhachHang kh, GoiHang gh, LoaiVanChuyen lvc where dh.MaGoiHang = gh.Ma and dh.MaLoaiVanChuyen = lvc.Ma and dh.MaKhachHang = kh.Ma and dh.TinhTrang = tt.Ma and dh.makhachhang=N'" + makhachhang + "'and (dh.TinhTrang = 1 or dh.TinhTrang = 2 or dh.TinhTrang = 3)");
            DataProvider dp = new DataProvider();
            DataTable dt = dp.ExecuteQuery(query);
            int i, n = dt.Rows.Count;
            arr = new DSDonHangDTO[n];
            for (i = 0; i < n; i++)
            {
                object gh = GetDataFromDataRow(dt, i);
                arr[i] = (DSDonHangDTO)gh;
            }
            return arr;
        }
        public DSDonHangDTO[] ChiTietDonHang(string madonhang)
        {
            DSDonHangDTO[] arr;
            string query = string.Format("select dh.ma,CONVERT(varchar(10), dh.ngaydat,103) as ngaydat,CONVERT(varchar(10), dh.ngaylay,103) as ngaylay,dh.diachilayhang,lvc.ten as tenlvc,dh.tennguoinhan,dh.cmnd,dh.sdt,dh.diachinguoinhan,dh.magoihang,kh.ten as tenkh,dh.tenchuthe,dh.sotaikhoan,dh.tongtien,tt.ten as tinhtrang,dh.loaidathang  from DonHang dh, TinhTrangDonHang tt, KhachHang kh, GoiHang gh, LoaiVanChuyen lvc where dh.MaGoiHang = gh.Ma and dh.MaLoaiVanChuyen = lvc.Ma and dh.MaKhachHang = kh.Ma and dh.TinhTrang = tt.Ma and dh.ma=N'" + madonhang + "'");
            DataProvider dp = new DataProvider();
            DataTable dt = dp.ExecuteQuery(query);
            int i, n = dt.Rows.Count;
            arr = new DSDonHangDTO[n];
            for (i = 0; i < n; i++)
            {
                object gh = GetDataFromDataRow(dt, i);
                arr[i] = (DSDonHangDTO)gh;
            }
            return arr;
        }
    }
}